package etf.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;


import java.sql.PreparedStatement;
import java.util.Random;

import etf.library.Address;
import etf.library.IndexedAddress;

public class SimpleJDBC {
	private Connection connection;

	
	public SimpleJDBC(){
		connection = JDBCUtil.getConnection();
		JDBCUtil.prepareStatements(connection);
	}
	
	public long insert(int brojSlogova) {
		long elapsedTime = 0;
		try {
			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			for (int i = 0; i < brojSlogova; i++) {
				Address address = new Address("Splitska_" + i,i, "Sarajevo", 71000, "BiH");
				JDBCUtil.setInsertAddress(address);
			}
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long indexedInsert(int brojSlogova) {
		String sql = "insert into indexed_address (street,house_number,city,zip_post_code,country) values (?,?,?,?,?)";
		PreparedStatement insertStatement = null;
		long elapsedTime = 0;
		try {
			insertStatement = connection.prepareStatement(sql);
			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			for (int i = 0; i < brojSlogova; i++) {
				IndexedAddress address = new IndexedAddress("Splitska_" + i,i, "Sarajevo", 71000, "BiH");
				insertStatement.setString(1, address.getStreet());
				insertStatement.setInt(2, address.getHouseNumber());
				insertStatement.setString(3, address.getCity());
				insertStatement.setInt(4, address.getZipPostCode());
				insertStatement.setString(5, address.getCountry());
				insertStatement.addBatch();
			}
			insertStatement.executeBatch();
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	public long deleteAll(String tableName){
		long elapsedTime = 0;
		String sql = "delete from " + tableName;
		Statement deleteAllStatement = null;
		try {
			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			deleteAllStatement = connection.createStatement();
			deleteAllStatement.executeUpdate(sql);
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long delete(int size){
		String sql = "delete from address where house_number =?";
		return deleteUtil(sql, size);
	}
	
	public long indexedDelete(int size){
		String sql = "delete from indexed_address where house_number =?";
		return deleteUtil(sql, size);
	}
	
	private long deleteUtil(String sql, int size){
		long elapsedTime = 0;
		PreparedStatement deleteStatement = null;
		try {
			connection.setAutoCommit(false);
			deleteStatement = connection.prepareStatement(sql);
			long startTime = System.currentTimeMillis();
			
			deleteStatement.setInt(1, getRandom(size));
			deleteStatement.execute();
			
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long query(int size){
		String sql = "select * from address where house_number =?";
		return queryUtil(sql, size);
	}
	
	public long indexedQueryInt(int size){
		String sql = "select * from indexed_address where house_number =?";
		return queryUtil(sql, size);
	}
	
	private long queryUtil(String sql, int size){
		long elapsedTime = 0;
		PreparedStatement selectStatement = null;
		try {
			
			long startTime = System.currentTimeMillis();
			
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setInt(1, getRandom(size));
			ResultSet rs = selectStatement.executeQuery();
			while (rs.next())
            {
				Address address = new Address(rs.getString("street"),rs.getInt("house_number"),rs.getString("city"),
						rs.getInt("zip_post_code"),rs.getString("country"));
				System.out.println("a:" + address.getStreet());
            }
					
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public long indexedQueryString (int size){
		long elapsedTime = 0;
		String sql = "select * from indexed_address where street =?";
		PreparedStatement selectStatement = null;
		try {
			
			long startTime = System.currentTimeMillis();
			
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setString(1, "Splitska_" + getRandom(size));
			ResultSet rs = selectStatement.executeQuery();
			while (rs.next())
            {
				Address address = new Address(rs.getString("street"),rs.getInt("house_number"),rs.getString("city"),
						rs.getInt("zip_post_code"),rs.getString("country"));
				System.out.println("a:" + address.getStreet());
            }
					
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public  long update (int size){
		long elapsedTime = 0;
		String sql = "update indexed_address set city = 'New_city'  where house_number =?";
		PreparedStatement selectStatement = null;
		try {
			
			long startTime = System.currentTimeMillis();
			
			connection.setAutoCommit(false);
			selectStatement = connection.prepareStatement(sql);
			selectStatement.setInt(1, getRandom(size));
			int rs = selectStatement.executeUpdate();
			System.out.println("jdbc:" + rs);
			
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	private int getRandom(int max){
		Random r = new Random();
        return r.nextInt(max); 
	}
	
}
